package com.bit.cfdi.web.dao.impl;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.bit.cfdi.web.dao.ConceptoDAO;
import com.bit.cfdi.web.domain.Comprobante;
import com.bit.cfdi.web.domain.Concepto;
import com.bit.cfdi.web.domain.ConceptoParte;
import com.bit.cfdi.web.domain.DBConstants;
import com.bit.cfdi.web.domain.Negocio;

@Repository
public class ConceptoDAOImpl extends HibernateDaoSupport implements ConceptoDAO {

	@Autowired
	public void init(final SessionFactory sessionFactory) {
		setSessionFactory(sessionFactory);
	}
	
	@Override
	@Transactional public void save(final Concepto concepto) throws DataAccessException {
		concepto.setStatus(DBConstants.STATUS_ACTIVE);
		getHibernateTemplate().saveOrUpdate(concepto);
	}
	
	@Override
	@Transactional public void delete(final Concepto concepto) throws DataAccessException {
		concepto.setStatus(DBConstants.STATUS_DELETED);
		getHibernateTemplate().saveOrUpdate(concepto);
		
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public Concepto getById(Long id) throws DataAccessException {
		List<Concepto> conceptos = getHibernateTemplate().find(
				" from Concepto as c" +
				" where c.idConcepto=?", id);
		return conceptos.size()>0?conceptos.get(0):null;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<Concepto> getAll() throws DataAccessException {
		List<Concepto> conceptos = getHibernateTemplate().find(
				" from Concepto as c");
		return conceptos;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public Concepto getByName(String name) throws DataAccessException {
		List<Concepto> conceptos = getHibernateTemplate().find(
				" from Concepto as c" +
				" where c.nombre=?", name);
		return conceptos.size()>0?conceptos.get(0):null;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<Concepto> getAllfromNegocio(Negocio negocio) throws DataAccessException {
		List<Concepto> conceptos = getHibernateTemplate().find(
				" from Concepto as c where c.negocio=?" 
				+" and  c.precioVenta is not null " 
				+" and c.status=?", negocio, DBConstants.STATUS_ACTIVE);
		return conceptos;
	}
	
	

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<Object[]> getBestClientsInTime(final Concepto concepto, 
			final Calendar initDate, 
			final Calendar finalDate) throws DataAccessException {
		getHibernateTemplate().setMaxResults(10);
		if(concepto!=null)
		{
 
		 	List<Object[]> results = getHibernateTemplate().find( 
				"select  c.cliente.persona.nombre , c.cliente.persona.apellidoPaterno, c.cliente.persona.apellidoMaterno, " +
				"sum(cc.cantidad),sum(cc.importe*c.tipoDeCambio)  "
				+" from ComprobanteConcepto as cc"
				 +" inner join cc.comprobante as c "   
				+" where "
				+" cc.concepto=?"
				+" AND ((c.parcialidades = c.numeroParcialidad) or (c.numeroParcialidad=0 and c.parcialidades=null))"
				+" AND ( c.tipoComprobante = '"+Comprobante.TIPO_COMP_INGRESO+"' OR c.tipoComprobante = '"+Comprobante.TIPO_COMP_TRASLADO+"'  )" 
				+" AND(( c.fechaTimbrado>?   AND c.fechaTimbrado<? )OR(c.fechaTimbrado is null AND c.fechaCreacion>?   AND c.fechaCreacion<? ))" 
				+" group by  c.cliente.persona.nombre , c.cliente.persona.apellidoPaterno, c.cliente.persona.apellidoMaterno  "
				+" order by sum(cc.importe)  desc ",
					    concepto,initDate, finalDate,initDate, finalDate);  
 
		 	getHibernateTemplate().setMaxResults(0);
			return results;
		}
		return null; 
	}
	
	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<ConceptoParte> getAllPartConceptos(Concepto concepto) throws DataAccessException {
		List<ConceptoParte> conceptos = getHibernateTemplate().find( 
				" from ConceptoParte as c where " 
				+"   c.concepto=?"
				+" and c.status=?", concepto, DBConstants.STATUS_ACTIVE);
 
		return conceptos;
		
	}
	
	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<Concepto> searchConceptByAllFields(final Negocio neg, 
			final String toSearch) throws DataAccessException {
		
		String toSearchLikeForm = "%"+toSearch+"%";
		
		List<Concepto> conceptos = getHibernateTemplate().find(
				" from Concepto as c where " +
				"c.negocio=? " +
				"and c.status=? " +
				" and  c.precioVenta is not null "+ 
				"and (LOWER(c.descripcion) like ? " +
				"or LOWER(c.numIdentificacion) like ? " +
				"or LOWER(c.codigobarras) like ? " +
				"or LOWER(c.unidad) like ?  ) ",
				neg, DBConstants.STATUS_ACTIVE,toSearchLikeForm.toLowerCase(),
				toSearchLikeForm.toLowerCase(), 	toSearchLikeForm.toLowerCase(),	
				toSearchLikeForm.toLowerCase() );
		return conceptos;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<Concepto> searchByDescUnit(final Negocio neg, 
			final String description, final String unit) throws DataAccessException {
		
		List<Concepto> conceptos = getHibernateTemplate().find(
				" from Concepto as c where " +
				" c.negocio=? " +
				" and c.status=? " +
				" and  c.precioVenta is not null "+ 
				"and LOWER(c.descripcion) = ? " +
				"and LOWER(c.unidad) = ? " ,
				neg, DBConstants.STATUS_ACTIVE,description.toLowerCase(),
				unit.toLowerCase()  );
		return conceptos;
	}
 
	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<Object[]> getTotalSalesInTimeByCategory(String category, 
			Concepto concept, Calendar initDate, Calendar finalDate) throws DataAccessException {
		List<Object[]> results = getHibernateTemplate().find(
				"select " +
				    "count(c), sum(cc.importe*c.tipoDeCambio) " +   
				"from " + 
				    " ComprobanteConcepto as cc " +
				    " inner join cc.comprobante as c " +
				" where " +  
				    "cc.concepto =? and " +
				    "c.categoriaComprobante=? and "+
				    "((c.parcialidades = c.numeroParcialidad) or (c.numeroParcialidad=0 and c.parcialidades=null)) and " +
				    "( c.tipoComprobante = '"+Comprobante.TIPO_COMP_INGRESO+"' OR c.tipoComprobante = '" + 
				    Comprobante.TIPO_COMP_TRASLADO+"'  ) and "+
					" (( c.fechaTimbrado>?   AND c.fechaTimbrado<? )OR(c.fechaTimbrado is null AND c.fechaCreacion>? " +
					" AND c.fechaCreacion<? ))"   ,
				    concept, category, initDate, finalDate,initDate, finalDate);
		return results;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<Object[]> getTotalSalesInTime( Concepto concept, 
			Calendar initDate, Calendar finalDate) throws DataAccessException {
		List<Object[]> results = getHibernateTemplate().find(
				"select " +
				    "count(cc), sum(cc.importe*c.tipoDeCambio) " +   
				"from " + 
				" ComprobanteConcepto as cc " +
			    " inner join cc.comprobante as c " +
				" where " +  
				    "cc.concepto =? and " + 
				    "((c.parcialidades = c.numeroParcialidad) or (c.numeroParcialidad=0 and c.parcialidades=null)) and " +
				    "( c.tipoComprobante = '"+Comprobante.TIPO_COMP_INGRESO+"' OR c.tipoComprobante = '" + 
				    Comprobante.TIPO_COMP_TRASLADO+"'  ) and "+
				    " (( c.fechaTimbrado>?   AND c.fechaTimbrado<? )OR(c.fechaTimbrado is null AND c.fechaCreacion>?  " +
				    " AND c.fechaCreacion<? ))" , concept,   initDate, finalDate, initDate, finalDate);
		return results;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public ArrayList<Object> getListaConceptos(String descripcionLike, 
			Negocio negocio) throws DataAccessException {
		String toSearchLikeForm = descripcionLike + "%";		
		List<String> results = getHibernateTemplate().find(
				"select concepto.descripcion from Concepto as concepto " +
				"where UPPER(concepto.descripcion) like ? " +
				"and concepto.status = ? " +
				"and concepto.negocio=? ", toSearchLikeForm.toUpperCase(), 
				DBConstants.STATUS_ACTIVE, negocio);
		ArrayList<Object> listaConceptos= new ArrayList<Object>();
		if(results.size()>0) {
			for (int i=0; i<results.size(); i++)
				listaConceptos.add(results.get(i));
		} else {
			listaConceptos = null;
		}
		return listaConceptos;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<Concepto> getByDescr(final Negocio negocio,final String descr) throws DataAccessException {
		if(negocio!=null &&descr!=null) {
			List<Concepto> conceptos = getHibernateTemplate().find( 
					" from Concepto as c where " 
					+"   c.negocio=?"
					+" and LOWER(c.descripcion)= ? ", negocio,descr);
			return conceptos;
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public Concepto getByNameNegocio(String name, Negocio negocio) throws DataAccessException {
		List<Concepto> conceptos = getHibernateTemplate().find(
				" from Concepto as c" +
				" where LOWER(c.descripcion)= ? " +
				" and c.negocio = ?", name, negocio);
		return conceptos.size()>0?conceptos.get(0):null;
	}

}
